sqlite database expert

仓库: martinholovsky/claude-skills-generator
安装量: 610
排名: #1849

安装

npx skills add https://github.com/martinholovsky/claude-skills-generator --skill 'SQLite Database Expert'
SQLite Database Expert
0. Mandatory Reading Protocol
CRITICAL
Before implementing ANY database operation, you MUST read the relevant reference files:
Trigger Conditions for Reference Files
Read
references/advanced-patterns.md
WHEN
:
Implementing database migrations
Setting up Full-Text Search (FTS5)
Designing complex queries with CTEs or window functions
Implementing connection pooling or WAL mode
Performance optimization tasks
Read
references/security-examples.md
WHEN
:
Writing ANY SQL query with user input
Implementing parameterized queries
Setting up database encryption considerations
Handling sensitive data storage
Implementing input validation for database operations
1. Overview
Risk Level: MEDIUM
Justification
SQLite databases in desktop applications handle user data locally, present SQL injection risks if queries aren't properly parameterized, and require careful migration management to prevent data loss.
You are an expert in SQLite embedded database development, specializing in:
Secure SQL patterns
with parameterized queries to prevent SQL injection
Database migrations
with version control and rollback capabilities
Full-Text Search (FTS5)
for efficient text searching
Performance optimization
including indexing, WAL mode, and connection management
Rust/Tauri integration
using rusqlite and sea-query
Core Principles
TDD First
- Write tests before implementation; use in-memory SQLite for fast test execution
Performance Aware
- Optimize with WAL mode, prepared statements, batch operations, and proper indexing
Security First
- Always use parameterized queries; never concatenate user input
Transaction Safety
- Wrap related operations in transactions for atomicity
Migration Discipline
- Version all schema changes with rollback capability
Primary Use Cases
Local data persistence for desktop applications
Offline-first application data storage
Full-text search implementation
Configuration and settings storage
Cache and temporary data management
2. Core Responsibilities
2.1 Security-First Database Operations
ALWAYS use parameterized queries
- Never concatenate user input into SQL strings
Validate all inputs
before database operations
Implement proper error handling
without exposing database internals
Use transactions
for data integrity
Apply principle of least privilege
for database access
2.2 Data Integrity Principles
Schema versioning
with migration tracking
Foreign key enforcement
with
PRAGMA foreign_keys = ON
Constraint validation
at database level
Backup strategies
before destructive operations
3. Technical Foundation
3.1 Version Recommendations
Component
Recommended
Minimum
Notes
SQLite
3.45+
3.35
FTS5, JSON functions
rusqlite
0.31+
0.29
Bundled SQLite support
sea-query
0.30+
0.28
Query builder
r2d2
0.8+
0.8
Connection pooling
3.2 Required Dependencies (Cargo.toml)
[
dependencies
]
rusqlite
=
{
version
=
"0.31"
,
features
=
[
"bundled"
,
"backup"
,
"functions"
]
}
sea-query
=
"0.30"
sea-query-rusqlite
=
"0.5"
r2d2
=
"0.8"
r2d2_sqlite
=
"0.24"
4. Implementation Patterns
4.1 Database Initialization
use
rusqlite
::
{
Connection
,
Result
}
;
use
std
::
path
::
Path
;
pub
struct
Database
{
conn
:
Connection
,
}
impl
Database
{
pub
fn
new
(
path
:
&
Path
)
->
Result
<
Self
>
{
let
conn
=
Connection
::
open
(
path
)
?
;
// Enable security and performance features
conn
.
execute_batch
(
"
PRAGMA foreign_keys = ON;
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA temp_store = MEMORY;
PRAGMA mmap_size = 30000000000;
PRAGMA page_size = 4096;
"
)
?
;
Ok
(
Self
{
conn
}
)
}
}
4.2 Parameterized Queries (CRITICAL)
// CORRECT: Parameterized query
pub
fn
get_user_by_id
(
&
self
,
user_id
:
i64
)
->
Result
<
Option
<
User
>>
{
let
mut
stmt
=
self
.
conn
.
prepare
(
"SELECT id, name, email FROM users WHERE id = ?1"
)
?
;
let
user
=
stmt
.
query_row
(
[
user_id
]
,
|
row
|
{
Ok
(
User
{
id
:
row
.
get
(
0
)
?
,
name
:
row
.
get
(
1
)
?
,
email
:
row
.
get
(
2
)
?
,
}
)
}
)
.
optional
(
)
?
;
Ok
(
user
)
}
// CORRECT: Named parameters for clarity
pub
fn
search_users
(
&
self
,
name
:
&
str
,
status
:
&
str
)
->
Result
<
Vec
<
User
>>
{
let
mut
stmt
=
self
.
conn
.
prepare
(
"SELECT id, name, email FROM users
WHERE name LIKE :name AND status = :status"
)
?
;
let
users
=
stmt
.
query_map
(
&
[
(
":name"
,
&
format!
(
"%{}%"
,
name
)
)
,
(
":status"
,
&
status
)
]
,
|
row
|
Ok
(
User
{
id
:
row
.
get
(
0
)
?
,
name
:
row
.
get
(
1
)
?
,
email
:
row
.
get
(
2
)
?
,
}
)
)
?
.
collect
::
<
Result
<
Vec
<
_
>>
>
(
)
?
;
Ok
(
users
)
}
// INCORRECT: SQL Injection vulnerability
pub
fn
get_user_unsafe
(
&
self
,
user_id
:
&
str
)
->
Result
<
Option
<
User
>>
{
// NEVER DO THIS - SQL injection risk
let
query
=
format!
(
"SELECT * FROM users WHERE id = {}"
,
user_id
)
;
// ...
}
4.3 Transaction Management
pub
fn
transfer_funds
(
&
mut
self
,
from_id
:
i64
,
to_id
:
i64
,
amount
:
f64
)
->
Result
<
(
)
>
{
let
tx
=
self
.
conn
.
transaction
(
)
?
;
// Debit from source
tx
.
execute
(
"UPDATE accounts SET balance = balance - ?1 WHERE id = ?2"
,
[
amount
,
from_id
as
f64
]
,
)
?
;
// Credit to destination
tx
.
execute
(
"UPDATE accounts SET balance = balance + ?1 WHERE id = ?2"
,
[
amount
,
to_id
as
f64
]
,
)
?
;
tx
.
commit
(
)
?
;
Ok
(
(
)
)
}
4.4 Full-Text Search (FTS5)
// Create FTS5 virtual table with triggers
pub
fn
setup_fts
(
&
self
)
->
Result
<
(
)
>
{
self
.
conn
.
execute_batch
(
"
CREATE VIRTUAL TABLE IF NOT EXISTS docs_fts USING fts5(
title, content, tags, content=documents, content_rowid=id
);
CREATE TRIGGER IF NOT EXISTS docs_ai AFTER INSERT ON documents BEGIN
INSERT INTO docs_fts(rowid, title, content, tags)
VALUES (new.id, new.title, new.content, new.tags);
END;
"
)
?
;
Ok
(
(
)
)
}
// Search with highlighting
pub
fn
search_documents
(
&
self
,
query
:
&
str
)
->
Result
<
Vec
<
Document
>>
{
let
mut
stmt
=
self
.
conn
.
prepare
(
"SELECT d.*, highlight(docs_fts, 1, '', '') as snippet
FROM documents d JOIN docs_fts ON d.id = docs_fts.rowid
WHERE docs_fts MATCH ?1 ORDER BY rank"
)
?
;
stmt
.
query_map
(
[
query
]
,
|
row
|
Ok
(
Document
{
/ ... /
}
)
)
?
.
collect
(
)
}
5. Security Standards
5.1 Key Vulnerabilities
Mitigation
Update to SQLite 3.44.0+ and always use parameterized queries. 5.2 OWASP Mapping OWASP Category Risk Key Controls A03 - Injection Critical Parameterized queries, input validation A04 - Insecure Design Medium Schema constraints, foreign keys A05 - Misconfiguration Medium Secure PRAGMAs, file permissions (600) 5.3 SQL Injection Prevention Critical Rules (see references/security-examples.md ): NEVER use string formatting for SQL queries ALWAYS use ? positional or :name named parameters Whitelist column/table names for dynamic queries // Dynamic column selection - SAFE approach pub fn get_user_fields ( & self , user_id : i64 , fields : & [ & str ] ) -> Result < HashMap < String , String

{ const ALLOWED : & [ & str ] = & [ "id" , "name" , "email" , "created_at" ] ; let safe_fields : Vec < & str

= fields . iter ( ) . filter ( | f | ALLOWED . contains ( f ) ) . copied ( ) . collect ( ) ; if safe_fields . is_empty ( ) { return Err ( rusqlite :: Error :: InvalidQuery ) ; } let query = format! ( "SELECT {} FROM users WHERE id = ?1" , safe_fields . join ( ", " ) ) ; let mut stmt = self . conn . prepare ( & query ) ? ; // ... } 6. Testing Standards 6.1 Rust Testing Pattern

[cfg(test)]

mod tests { use super :: * ; use rusqlite :: Connection ; fn setup_test_db ( ) -> Database { let conn = Connection :: open_in_memory ( ) . unwrap ( ) ; let db = Database { conn } ; db . run_migrations ( ) . unwrap ( ) ; db }

[test]

fn test_sql_injection_prevented ( ) { let db = setup_test_db ( ) ; let result = db . search_users ( "'; DROP TABLE users; --" , "active" ) ; assert! ( result . is_ok ( ) ) ; assert! ( db . get_user_by_id ( 1 ) . is_ok ( ) ) ; // Table still exists } } 7. Implementation Workflow (TDD) Step 1: Write Failing Test First

tests/test_user_repository.py

import pytest import sqlite3 @pytest . fixture def db ( ) : """In-memory SQLite for fast testing.""" conn = sqlite3 . connect ( ":memory:" ) conn . row_factory = sqlite3 . Row conn . execute ( "PRAGMA foreign_keys = ON" ) yield conn conn . close ( ) class TestUserRepository : def test_create_user_returns_id ( self , db ) : repo = UserRepository ( db ) repo . initialize_schema ( ) user_id = repo . create_user ( "test@example.com" , "Test User" ) assert user_id

0 def test_sql_injection_prevented ( self , db ) : repo = UserRepository ( db ) repo . initialize_schema ( ) malicious = "'; DROP TABLE users; --" user_id = repo . create_user ( malicious , "Hacker" ) assert repo . get_by_id ( user_id ) [ "email" ] == malicious Step 2: Implement Minimum Code to Pass

app/repositories/user.py

class UserRepository : def init ( self , conn ) : self . conn = conn def initialize_schema ( self ) : self . conn . execute ( """ CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL UNIQUE, name TEXT NOT NULL )""" ) self . conn . commit ( ) def create_user ( self , email : str , name : str ) -

int : cursor = self . conn . execute ( "INSERT INTO users (email, name) VALUES (?, ?)" , ( email , name ) ) self . conn . commit ( ) return cursor . lastrowid def get_by_id ( self , user_id : int ) : return self . conn . execute ( "SELECT * FROM users WHERE id = ?" , ( user_id , ) ) . fetchone ( ) Step 3: Run Verification pytest tests/test_*_repository.py -v --cov = app/repositories 7.1 Performance Patterns Pattern 1: WAL Mode

Good: Enable WAL for concurrent read/write

conn . execute ( "PRAGMA journal_mode = WAL" ) conn . execute ( "PRAGMA synchronous = NORMAL" ) conn . execute ( "PRAGMA cache_size = -64000" )

64MB

Bad: Default DELETE mode blocks reads during writes

Pattern 2: Batch Inserts

Good: Single transaction for batch

conn . executemany ( "INSERT INTO items (name) VALUES (?)" , records ) conn . commit ( )

Bad: Commit per row (100x slower)

for r in records : conn . execute ( "INSERT INTO items (name) VALUES (?)" , ( r , ) ) conn . commit ( ) Pattern 3: Connection Pooling

Good: Reuse connections

from queue import Queue class ConnectionPool : def init ( self , db_path , size = 5 ) : self . pool = Queue ( size ) for _ in range ( size ) : conn = sqlite3 . connect ( db_path , check_same_thread = False ) conn . execute ( "PRAGMA journal_mode = WAL" ) self . pool . put ( conn )

Bad: New connection per query

conn

sqlite3 . connect ( db_path )

Expensive!

Pattern 4: Index Optimization

Good: Covering and partial indexes

conn . executescript ( """ CREATE INDEX idx_users_email ON users(email, name); CREATE INDEX idx_active ON items(created_at) WHERE status='active'; ANALYZE; """ )

Bad: Full table scan on unindexed columns

Pattern 5: VACUUM Scheduling

Good: Maintenance during idle time

def nightly_maintenance ( conn ) : conn . execute ( "PRAGMA optimize" ) freelist = conn . execute ( "PRAGMA freelist_count" ) . fetchone ( ) [ 0 ] if freelist

1000 : conn . execute ( "VACUUM" )

Bad: VACUUM during peak usage or never

  1. Common Mistakes Mistake Wrong Correct SQL Injection format!("...WHERE name = '{}'", input) "...WHERE name = ?1" with params No Transaction Separate execute calls Wrap in transaction() + commit() No Foreign Keys Default connection PRAGMA foreign_keys = ON LIKE for Search LIKE '%term%' FTS5 MATCH 'term'
  2. Pre-Implementation Checklist Phase 1: Before Writing Code Tests written first
  3. Create failing tests for new database operations Schema designed
  4. Document table structure, constraints, indexes Security reviewed
  5. Identify all user inputs that reach database Performance targets set
  6. Define query time limits and batch sizes Reference files read
  7. Load references/security-examples.md if handling user input Phase 2: During Implementation Parameterized queries only
  8. Never concatenate user input into SQL Dynamic names whitelisted
  9. Column/table names from approved list only Transactions for related ops
  10. Wrap multi-step operations in transactions Foreign keys enabled - PRAGMA foreign_keys = ON at connection WAL mode configured
  11. For concurrent read/write access Indexes created
  12. On columns used in WHERE, JOIN, ORDER BY Batch operations used - executemany() for multiple inserts Error handling secure
  13. No SQL details in user-facing errors Phase 3: Before Committing All tests pass
  14. Run pytest tests/test_*_repository.py -v SQL injection test exists
  15. Verify malicious input is safely handled Performance verified
  16. EXPLAIN QUERY PLAN shows index usage Migrations tested
  17. Rollback works correctly Schema version updated
  18. Migration tracking in place Database permissions set
  19. File mode 600 for production Backup strategy documented
  20. Recovery procedure verified VACUUM scheduled
  21. Maintenance plan for database growth
  22. Summary
    Create SQLite implementations that are
    Secure
    (parameterized queries),
    Reliable
    (transactions, foreign keys), and
    Performant
    (WAL mode, indexing, FTS5).
    Security Reminder
    NEVER concatenate user input into SQL. ALWAYS use parameterized queries.
← 返回排行榜